# -*- coding: utf-8 -*-
"""
Spyder 编辑器

这是一个临时脚本文件。
"""

import pandas as pd
import pymysql
from sqlalchemy import create_engine
conn=create_engine('mysql+pymysql://root:123456@localhost:3306/aaa?charset=utf8')
df=pd.read_sql("select * from tbl_course",conn)
cs=pd.read_csv("C:\\Users\\PC\\Desktop\\lecturer.csv")
df=pd.merge(df,cs,how="inner",left_on="lecturer_id",right_on="id")
df=df[df['级别']!='C']
df1=df[df['class_hours'].str[2]==',']
df2=df[df['class_hours'].str[2]!=',']
df1['class_hours']=df1['class_hours'].str[0:2]
df=pd.concat([df1,df2],axis=0)
df['class_hours']=df['class_hours'].astype('int')
df['class_weeks']=df['class_hours']/df['hours_per_week']
df['class_weeks']=df['class_weeks'].astype('int')
df=df[['id_x','name','lecturer','class_weeks']]
df=df.sort_values(by="id_x" , ascending=False) 
df.to_csv("C:\\Users\\PC\\Desktop\\1bpython.csv")